package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Set;

import entitys.Word;

public class WordDAO {

	private Connection con = null;
	PreparedStatement ps = null;
	ResultSet rs = null;

	public WordDAO() {
	}

	private Connection getConnection() throws SQLException {
		Connection conn;
		conn = (Connection) ConnectionFactory.getInstance().getConnection();
		return conn;
	}

	public void add(Word word) {
		try {
			String queryString = "INSERT INTO words(value, translation, count) VALUES(?,?,?)";
			con = getConnection();
			ps = con.prepareStatement(queryString);
			ps.setString(1, word.getValue());
			ps.setString(2, word.getTranslation());
			ps.setInt(3, word.getCount());
//			ps.setLong(4, word.getLastDate());
//			ps.setInt(5, word.getKnown());
//			ps.setInt(6, word.getWordNew());
//			ps.setInt(7, word.getStudys());
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			try {
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public void updateTranslation(Word word) {

		try {
			String queryString = "UPDATE words SET translation=? WHERE value=?";
			con = getConnection();
			ps = con.prepareStatement(queryString);
			ps.setString(1, word.getTranslation());
			ps.setString(2, word.getValue());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public void updateCount(Word word) {

		try {
			String queryString = "UPDATE words SET count=? WHERE value=?";
			con = getConnection();
			ps = con.prepareStatement(queryString);
			ps.setInt(1, word.getCount());
			ps.setString(2, word.getValue());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}


	public void updateStudys(Word word) {

		try {
			String queryString = "UPDATE words SET studys=? WHERE value=?";
			con = getConnection();
			ps = con.prepareStatement(queryString);
			ps.setInt(1, word.getStudys());
			ps.setString(2, word.getValue());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}


	public void updateKnown(Word word) {

		try {
			String queryString = "UPDATE words SET known=? WHERE value=?";
			con = getConnection();
			ps = con.prepareStatement(queryString);
			ps.setInt(1, word.getKnown());
			ps.setString(2, word.getValue());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}


	public void updateWordNew(Word word) {

		try {
			String queryString = "UPDATE words SET wordNew=? WHERE value=?";
			con = getConnection();
			ps = con.prepareStatement(queryString);
			ps.setInt(1, word.getWordNew());
			ps.setString(2, word.getValue());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	
	
	public void delete(String value) {

		try {
			String queryString = "DELETE FROM words WHERE value=?";
			con = getConnection();
			ps = con.prepareStatement(queryString);
			ps.setString(1, value);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public Set<Word> getAll() {

		Set<Word> set = new HashSet<>();

		try {

			String queryString = "SELECT * FROM words";
			con = getConnection();
			ps = con.prepareStatement(queryString);
			rs = ps.executeQuery();

			while (rs.next()) {
				Word word = new Word();
				 word.setValue(rs.getString("value"));
				 word.setTranslation(rs.getString("translation"));
				 word.setCount(rs.getInt("count"));
				 word.setLastDate(rs.getLong("lastDate"));
				 word.setKnown(rs.getInt("known"));
				 word.setWordNew(rs.getInt("wordNew"));
				 word.setStudys(rs.getInt("studys"));
				set.add(word);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return set;
	}
	
	public ArrayList<Word> getNumberOfRows() {
		
		Integer rowsNum = 20;
//		SELECT * FROM words where count between (select max(count) from words)-1 and (select max(count) from words)  order by count desc limit 20;
//		SELECT w.* FROM words w, (SELECT MAX(count) count, lastDate FROM words GROUP BY lastDate) w2 WHERE w.count=w2.count AND w.lastDate=w2.lastDate LIMIT 2
//		"SELECT w.* FROM words w, (SELECT MAX(count) count, lastDate FROM words GROUP BY lastDate) w2
//		WHERE w.count=w2.count AND w.lastDate=w2.lastDate LIMIT "
		String stringSelect = "SELECT * FROM words WHERE known != 1 AND studys = 0 ORDER BY count DESC limit ";
		String stringLimit = stringSelect + rowsNum;

		ArrayList<Word> selectedWords = new ArrayList<Word>();

		try {

			String queryString = stringLimit;
			con = getConnection();
			ps = con.prepareStatement(queryString);
			rs = ps.executeQuery();

			while (rs.next()) {
				Word word = new Word();
				 word.setValue(rs.getString("value"));
				 word.setTranslation(rs.getString("translation"));
				 word.setCount(rs.getInt("count"));
				 word.setLastDate(rs.getLong("lastDate"));
				 word.setKnown(rs.getInt("known"));
				 word.setWordNew(rs.getInt("wordNew"));
				 word.setStudys(rs.getInt("studys"));
				selectedWords.add(word);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return selectedWords;
	}
	
	

	public Word findByValue(String value) {
		Word word = null;

		try {

			String queryString = "SELECT * FROM words WHERE value=?";
			con = getConnection();
			ps = con.prepareStatement(queryString);
			ps.setString(1, value);
			rs = ps.executeQuery();

			if(rs.next()){
				word =  new Word();
				 word.setValue(rs.getString("value"));
				 word.setTranslation(rs.getString("translation"));
				 word.setCount(rs.getInt("count"));
				 word.setLastDate(rs.getLong("lastDate"));
				 word.setKnown(rs.getInt("known"));
				 word.setWordNew(rs.getInt("wordNew"));
				 word.setStudys(rs.getInt("studys"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return word;
	}
}
